﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Collections;
using System.Data.OleDb;
using System.Data;

namespace WebAddressBook
{
    public partial class BatchInput : BasePage
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void BtUpload_Click(object sender, EventArgs e)
        {
            try
            {
                bool b = UploadExcelToServer();  // 上传excel文件 
                if (!b)
                    return;
                string name = FileUploadExecl.FileName;
                string filepath = Server.MapPath("~/UploadFile/") + name;
                DataSet ds = GetDataFromExecl(filepath);
                InsertDataToDb(ds);
            }
            catch (Exception ex) {
                LbMessage.Text = ex.Message;
            }
        }

        private void InsertDataToDb(DataSet ds)
        {
            if (ds == null || ds.Tables[0].Rows.Count < 1)
            {
                LbMessage.Text = "<div class='box box-error'>该Execl没有数据！</div>";
                return;
            }
            DataSet deptDs = GetAllDeptList();
            //把所有的部门名称替换成部门id,男\女替换成True\False
            try
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    dr["性别"] = dr["性别"].ToString().Trim() == "男" ? "True" : "False";
                    string enterpirseName = dr["所属企业"].ToString().Trim();
                    string deptName = dr["所属部门"].ToString().Trim();
                    string enterpirseId = GetEnterpriseId(enterpirseName, deptDs);
                    //dr["所属部门"] = ReplaceDeptNameToDeptId(dr["所属部门"].ToString(), deptDs);
                    dr["所属部门"] = ReplaceDeptNameToDeptId(deptName, enterpirseName, enterpirseId, deptDs);
                }
            }
            catch (Exception ex)
            {
                LbMessage.Text = "<div class='box box-error'>"+ex.Message+"</div>";
                return;
            }

            InsertData(ds);
            LbMessage.Text = "<div class='box box-info'>通讯录导入成功！</div>";
        }

        private void InsertData(DataSet ds)
        {
            Adrr.BLL.Base_Employee baseEmployeeBll = new Adrr.BLL.Base_Employee();
            string dataServerUrl = CommonHelper.DataServerUrl;
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                Adrr.Model.Base_Employee baseEmployeeModel = new Adrr.Model.Base_Employee();
                baseEmployeeModel.FstName = dr["姓"].ToString();
                baseEmployeeModel.SecName = dr["名"].ToString();
                baseEmployeeModel.Sex = Convert.ToBoolean(dr["性别"].ToString());
                baseEmployeeModel.DeptID = dr["所属部门"].ToString();
                baseEmployeeModel.Phone = dr["手机号码"].ToString();
                baseEmployeeModel.Station = dr["职位"].ToString();
                baseEmployeeModel.Tel = dr["办公座机"].ToString();
                baseEmployeeModel.FamiryTel = dr["家庭电话"].ToString();
                baseEmployeeModel.Fax = dr["传真"].ToString();
                baseEmployeeModel.Email = dr["Email"].ToString();
                baseEmployeeModel.Address = dr["地址"].ToString();
                baseEmployeeModel.HeadUrl = dr["头像"].ToString();
                baseEmployeeModel.isDelete = false;
                baseEmployeeModel.CreateTime = DateTime.Now;
                baseEmployeeModel.UpdateTime = DateTime.Now;
                baseEmployeeModel.FromServices = dataServerUrl;

                try
                {
                    baseEmployeeBll.Add(baseEmployeeModel);
                }
                catch
                {
                    LbMessage.Text = "<div class='box box-error'>导入通讯录失败！</div>";
                }
            }
        }
        private DataSet GetAllDeptList()
        {
            Adrr.BLL.Base_Dept baseDeptBll = new Adrr.BLL.Base_Dept();
            return baseDeptBll.GetDeptIdAndDeptName();
        }
        private string ConvertSexToCode(string sex)
        {
            return sex == "男" ? "True" : "False";
        }
        /// <summary>
        /// 根据部门的名字拿到部门的id号
        /// </summary>
        //private string ReplaceDeptNameToDeptId(string deptName, DataSet ds)
        //{
        //    foreach (DataRow dr in ds.Tables[0].Rows)
        //    {
        //        if (dr["DeptName"].ToString() == deptName)
        //            return dr["DeptID"].ToString();
        //    }
        //    return "0000";
        //}

        private string ReplaceDeptNameToDeptId(string deptName,string enterpriseName, string enterpirseId, DataSet ds)
        {
            DataRow[] drsDept = ds.Tables[0].Select("DeptID like '" + enterpirseId + "%'");
            //ds.Tables[0].Select("DeptID like '" + enterpirseId + "%' and DeptID!='"+enterpirseId+"'");
            foreach (DataRow dr in drsDept)
            {
                if (dr["DeptName"].ToString() == deptName)
                    return dr["DeptID"].ToString();
            }
            Exception ex = new Exception(enterpriseName + "的部门：" + deptName + "：在数据库里面不存在！");
            throw ex;
        }
        /// <summary>
        /// 根据企业名字拿企业id
        /// </summary>
        private string GetEnterpriseId(string enterpriseName, DataSet ds)
        {
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                if (dr["DeptName"].ToString() == enterpriseName)
                    return dr["DeptID"].ToString();
            }
            Exception ex = new Exception("企业：" + enterpriseName + "：在数据库不存在！");
            throw ex;
        }
        /// <summary>
        /// 上传excel文件到服务器
        /// </summary>
        private bool UploadExcelToServer()
        {
            bool flag = false;
            //是否允许上载  
            bool fileAllow = false;
            //设定允许上载的扩展文件名类型  
            string[] allowExtensions = { ".xls", ".xlsx" };

            //取得网站根目录路径  
            string path = HttpContext.Current.Request.MapPath("~/UploadFile/");
            if (FileUploadExecl.HasFile)
            {
                string fileExtension = System.IO.Path.GetExtension(FileUploadExecl.FileName).ToLower();
                for (int i = 0; i < allowExtensions.Length; i++)
                {
                    if (fileExtension == allowExtensions[i])
                    {
                        fileAllow = true;
                        break;
                    }
                }

                if (fileAllow)
                {
                    try
                    {
                        //存储文件到文件夹 
                        FileUploadExecl.SaveAs(path + FileUploadExecl.FileName);
                        //LbMessage.Text = "<div class='box box-info'>文件导入成功！</div>";
                        flag = true;
                    }
                    catch (Exception ex)
                    {
                        LbMessage.Text += ex.Message;
                        flag = false;
                    }
                }
                else
                {
                    LbMessage.Text = "<div class='box box-error'>不允许上载：" + FileUploadExecl.PostedFile.FileName + "，只能上传xls或xlsx的文件，请检查！</div>";
                    flag = false;
                }
            }
            else
            {
                LbMessage.Text = "<div class='box box-error'>请选择要导入的excel文件!</div>";
                flag = false;
            }

            return flag;
        }

        /// <summary>
        /// 该方法实现从Excel中导出数据到DataSet中，其中filepath为Excel文件的绝对路径， sheetname为excel文件中的表名
        /// </summary>
        private DataSet GetDataFromExecl(string FilePath)
        {
            //03的excel，暂时支持03 用这个
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1';";//不能是11.0 
            //07的excel用这个
            //string strConn = @"Provider=Microsoft.ACE.OLEDB.12.0; Persist Security Info=False;Data Source=" + FilePath + "; Extended Properties=Excel 8.0";

            OleDbConnection conn = new OleDbConnection(strConn);
            DataSet ds = new DataSet();
            try
            {
                conn.Open();
                string strExcel = "select * from [sheet1$]";
                OleDbDataAdapter da = new OleDbDataAdapter(strExcel, strConn);
                da.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

    }
}